﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;

namespace GroupProject
{

    public partial class frmCustomers : Form
    {
        SqlConnection con;
        SqlCommand cmd;
        const string constr = @"server=.;database=TSQLFundamentals2008;uid=sa;pwd=01285778433";
        public frmCustomers()
        {
            InitializeComponent();
            InitializeAOObject();
            loadData();
        }


        void InitializeAOObject()
        {
            con = new SqlConnection();
            con.ConnectionString  = ConfigurationManager.ConnectionStrings["SqlStr"].ConnectionString; ;
            con.Open();
            cmd = new SqlCommand();
            cmd.Connection = con;
        }
        public bool valid()
        {
            bool valid = true;
            if (txtcompName.Text.Length == 0)
            {
                errorProvider1.SetError(txtcompName, "Company Name cannot empty");
                valid = false;
            }
            if (txtContName.Text.Length == 0)
            {
                errorProvider1.SetError(txtContName, "Contact Name cannot empty");
                valid = false;
            }
            if (txtTitlename.Text.Length == 0)
            {
                errorProvider1.SetError(txtTitlename, "Title Name cannot empty");
                valid = false;
            }
            if (txtAddress.Text.Length == 0)
            {
                errorProvider1.SetError(txtAddress, "Address cannot empty");
                valid = false;
            }
            if (txtCity.Text.Length == 0)
            {
                errorProvider1.SetError(txtCity, "City cannot empty");
                valid = false;
            }


            
            if (cbbCountry.Text.Length == 0)
            {
                errorProvider1.SetError(cbbCountry, "Country cannot empty");
                valid = false;
            }
            if (txtPosCode.Text.Trim().Length!=0)
            {
                if (!txtPhone.MaskFull)
                {
                    errorProvider1.SetError(txtPhone, "Phone not accept");
                    valid = false;
                }
            }
            if (txtFax.Text.Trim().Length!=0)
            {
                if (!txtFax.MaskFull)
                {
                    errorProvider1.SetError(txtFax, "Fax not accept");
                    valid = false;
                }
            }
         
            //if (txtPhone.Text.Length == 0)
            //{
            //    errorProvider1.SetError(txtPhone, "Phone cannot empty");
            //    valid = false;
            //}



            if (valid == false)
            {
                return false;
            }
            return true;
        }
        void loadData()
        {
            try
            {
                InitializeAOObject();

                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_getcustomer";
                SqlDataReader dr = cmd.ExecuteReader();
                gvCustomers.Rows.Clear();
                while (dr.Read())
                {
                    gvCustomers.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(), dr[3].ToString(), dr[4].ToString(),
                        dr[5].ToString(), dr[6].ToString(), dr[7].ToString(), dr[8].ToString(), dr[9].ToString(), dr[10].ToString());
                }
                dr.Close();
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message);
            }
            finally
            {

                con.Close();
            }
        }
        #region INSERT,Update,DELETE,Search
        bool insertCustomer()
        {
            bool result;
            try
            {
                InitializeAOObject();

                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_insertCustomer";

                SqlParameter para = new SqlParameter("@companyName", SqlDbType.NVarChar);
                para.Size = 40;
                para.Value = txtcompName.Text.Trim();
                cmd.Parameters.Add(para);

                para = new SqlParameter("@contactname", SqlDbType.NVarChar);
                para.Size = 30;
                para.Value = txtContName.Text.Trim();
                cmd.Parameters.Add(para);

                para = new SqlParameter("@contacttitle", SqlDbType.NVarChar);
                para.Size = 30;
                para.Value = txtTitlename.Text.Trim();
                cmd.Parameters.Add(para);

                para = new SqlParameter("@address", SqlDbType.NVarChar);
                para.Size = 60;
                para.Value = txtAddress.Text.Trim();
                cmd.Parameters.Add(para);

                para = new SqlParameter("@city", SqlDbType.NVarChar);
                para.Size = 15;
                para.Value = txtCity.Text.Trim();
                cmd.Parameters.Add(para);

                para = new SqlParameter("@region", SqlDbType.NVarChar);
                if (txtRegion.Text.Trim().Length == 0)
                {
                    para.Size = 15;
                    para.Value = DBNull.Value;
                    cmd.Parameters.Add(para);
                }
                else
                {
                    para.Size = 15;
                    para.Value = txtRegion.Text.Trim();
                    cmd.Parameters.Add(para);
                }
                para = new SqlParameter("@postalcode", SqlDbType.NVarChar);
                if (txtPosCode.Text.Trim().Length == 0)
                {
                    para.Size = 10;
                    para.Value = DBNull.Value;
                    cmd.Parameters.Add(para);
                }
                else
                {
                    para.Size = 10;
                    para.Value = txtPosCode.Text.Trim();
                    cmd.Parameters.Add(para);
                }
                para = new SqlParameter("@country", SqlDbType.NVarChar);
                para.Size = 15;
                para.Value = cbbCountry.Text.Trim();
                cmd.Parameters.Add(para);

                para = new SqlParameter("@phone", SqlDbType.NVarChar);
                
                    para.Size = 24;
                    para.Value = txtPhone.Text.Trim();
                    cmd.Parameters.Add(para);
                
                para = new SqlParameter("@fax", SqlDbType.NVarChar);
                if (txtFax.Text.Trim().Length == 0)
                {
                    para.Size = 24;
                    para.Value = DBNull.Value;
                    cmd.Parameters.Add(para);
                }
                else
                {
                    para.Size = 24;
                    para.Value = txtFax.Text.Trim();
                    cmd.Parameters.Add(para);
                }
                cmd.ExecuteNonQuery();
                result = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                result = false;
            }
            finally
            {
                con.Close();
            }
            if (result==false)
            {
                return false;
            }
            return true;
        }
        bool UpdateCustomer()
        {
            bool result;
            try
            {
                InitializeAOObject();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_UpdateCustomer";

                SqlParameter para = new SqlParameter("@cusID", SqlDbType.Int);
                para.Value = txtID.Text;
                cmd.Parameters.Add(para);

                para = new SqlParameter("@companyName", SqlDbType.NVarChar);
                para.Size = 40;
                para.Value = txtcompName.Text;
                cmd.Parameters.Add(para);

                para = new SqlParameter("@contactname", SqlDbType.NVarChar);
                para.Size = 30;
                para.Value = txtContName.Text;
                cmd.Parameters.Add(para);

                para = new SqlParameter("@contacttitle", SqlDbType.NVarChar);
                para.Size = 30;
                para.Value = txtTitlename.Text;
                cmd.Parameters.Add(para);

                para = new SqlParameter("@address", SqlDbType.NVarChar);
                para.Size = 60;
                para.Value = txtAddress.Text;
                cmd.Parameters.Add(para);

                para = new SqlParameter("@city", SqlDbType.NVarChar);
                para.Size = 15;
                para.Value = txtCity.Text;
                cmd.Parameters.Add(para);

                para = new SqlParameter("@region", SqlDbType.NVarChar);
                if (txtRegion.Text.Trim().Length == 0)
                {
                    para.Size = 15;
                    para.Value = DBNull.Value;
                    cmd.Parameters.Add(para);
                }
                else
                {
                    para.Size = 15;
                    para.Value = txtRegion.Text.Trim();
                    cmd.Parameters.Add(para);
                }
                para = new SqlParameter("@postalcode", SqlDbType.NVarChar);
                if (txtPosCode.Text.Trim().Length == 0)
                {
                    para.Size = 10;
                    para.Value = DBNull.Value;
                    cmd.Parameters.Add(para);
                }
                else
                {
                    para.Size = 10;
                    para.Value = txtPosCode.Text.Trim();
                    cmd.Parameters.Add(para);
                }

                para = new SqlParameter("@country", SqlDbType.NVarChar);
                para.Size = 15;
                para.Value = cbbCountry.Text;
                cmd.Parameters.Add(para);

                para = new SqlParameter("@phone", SqlDbType.NVarChar);
                para.Size = 24;
                para.Value = txtPhone.Text;
                cmd.Parameters.Add(para);

                para = new SqlParameter("@fax", SqlDbType.NVarChar);
                if (txtFax.Text.Trim().Length == 0)
                {
                    para.Size = 24;
                    para.Value = DBNull.Value;
                    cmd.Parameters.Add(para);
                }
                else
                {
                    para.Size = 24;
                    para.Value = txtFax.Text.Trim();
                    cmd.Parameters.Add(para);
                }

                cmd.ExecuteNonQuery();
                result = true;
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message);
                result = false;
            }
            finally
            {
                con.Close();
            }
            return result;
        }
        bool deleteCustomer()
        {
            bool result = true;
            try
            {
                InitializeAOObject();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_deleteCustomer";

                SqlParameter para = new SqlParameter("@cusID", SqlDbType.Int);
                para.Value = Int32.Parse(txtID.Text);
                cmd.Parameters.Add(para);
                cmd.ExecuteNonQuery();
                result = true;
            }
            catch (Exception ex)
            {
                result = false;
                MessageBox.Show(ex.Message);

            }
            finally
            {
                con.Close();
            }
            return result;
        }
        void clear()
        {
            errorProvider1.Clear();
            txtID.Clear();
            txtcompName.Clear();
            txtContName.Clear();
            txtTitlename.Clear();
            txtAddress.Clear();
            txtCity.Clear();
            txtRegion.Clear();
            txtPosCode.Clear();
            cbbCountry.SelectedIndex = -1;
            txtPhone.Clear();
            txtFax.Clear();
        }
        void searchID()
        {
            try
            {
                if (txtSearch.Text.Length == 0)
                {
                    MessageBox.Show("Please Enter an Key");
                    return;
                }
                InitializeAOObject();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_SearchCustomer";

                SqlParameter para = new SqlParameter("@custID", SqlDbType.Int);
                para.Value = txtSearch.Text;

                cmd.Parameters.Add(para);
                String returnID = cmd.ExecuteScalar().ToString();

                cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_getcustomer";
                SqlDataReader dr = cmd.ExecuteReader();
                gvCustomers.Rows.Clear();
                while (dr.Read())
                {
                    if (dr[0].ToString().Equals(returnID))
                    {

                        gvCustomers.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(), dr[3].ToString(), dr[4].ToString(), dr[5].ToString(),
                            dr[6].ToString(), dr[7].ToString(), dr[8].ToString(), dr[9].ToString(), dr[10].ToString());
                    }
                }
                dr.Close();
            }

            catch (Exception )
            {

                MessageBox.Show("ID " + txtSearch.Text + " is not exist");
                gvCustomers.Rows.Clear();
            }

           
            finally
            {
                con.Close();
            }

         

        }
        void searchbyName()
        {
            try
            {
                if (txtSearch.Text.Length == 0)
                {
                    MessageBox.Show("Please Enter an Key");
                    return;
                }
                InitializeAOObject();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_SearchCustomerName";

                SqlParameter para = new SqlParameter("@contactname", SqlDbType.NVarChar);
                para.Size = 30;
                para.Value = txtSearch.Text;

                cmd.Parameters.Add(para);
                String returnName = cmd.ExecuteScalar().ToString();
                
                cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_getcustomer";
                SqlDataReader dr = cmd.ExecuteReader();
                gvCustomers.Rows.Clear();
                while (dr.Read())
                {
                    if (dr[0].ToString().Equals(returnName))
                    {

                        gvCustomers.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(), dr[3].ToString(), dr[4].ToString(), dr[5].ToString(),
                            dr[6].ToString(), dr[7].ToString(), dr[8].ToString(), dr[9].ToString(), dr[10].ToString());
                    }
                }
                dr.Close();
            }

            

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                 MessageBox.Show(txtSearch.Text + " is not exist in Data");
                gvCustomers.Rows.Clear();
            }
            finally
            {
                con.Close();
            }

               
          
         

        }
        void searchByTitile()
        {
            InitializeAOObject();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_getcustomer";
            SqlDataReader dr = cmd.ExecuteReader();
            gvCustomers.Rows.Clear();
            while (dr.Read())
            {
                if (dr[3].ToString().Equals(cbbOption.Text))
                {
                    gvCustomers.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(), dr[3].ToString(), dr[4].ToString(),
                        dr[5].ToString(), dr[6].ToString(), dr[7].ToString(), dr[8].ToString(), dr[9].ToString(), dr[10].ToString());
                }
            }
            dr.Close();
            con.Close();

        }
        void searchbyCountry()
        {
            InitializeAOObject();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_getcustomer";
            SqlDataReader dr = cmd.ExecuteReader();
            gvCustomers.Rows.Clear();
            while (dr.Read())
            {
                if (dr[8].ToString().Equals(cbbOption.Text))
                {
                    gvCustomers.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(), dr[3].ToString(), dr[4].ToString(),
                    dr[5].ToString(), dr[6].ToString(), dr[7].ToString(), dr[8].ToString(), dr[9].ToString(), dr[10].ToString());
                }
            }
            dr.Close();
            con.Close();
        }
        #endregion
        private void btnInsert_Click(object sender, EventArgs e)
        {
            bool chk;
            if (!valid())
            {
                return;
            }
            chk=insertCustomer();
            if (chk==true)
            {
                MessageBox.Show("Insert successful");
            }
            else
            {
                MessageBox.Show("Insert fail!");
            }

            loadData();
        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            if (cbbFilter.Text == "ID")
            {
                searchID();
            }
            if (cbbFilter.Text == "CONTACT NAME")
            {
                searchbyName();
            }


        }

        private void gvCustomers_SelectionChanged(object sender, EventArgs e)
        {
            if (gvCustomers.SelectedCells.Count > 1)
            {
                DataGridViewRow r = gvCustomers.SelectedRows[0];
                txtID.Text = r.Cells[0].Value.ToString();
                txtcompName.Text = r.Cells[1].Value.ToString();
                txtContName.Text = r.Cells[2].Value.ToString();
                txtTitlename.Text = r.Cells[3].Value.ToString();
                txtAddress.Text = r.Cells[4].Value.ToString();
                txtCity.Text = r.Cells[5].Value.ToString();
                txtRegion.Text = r.Cells[6].Value.ToString();
                txtPosCode.Text = r.Cells[7].Value.ToString();
                cbbCountry.Text = r.Cells[8].Value.ToString();
                txtPhone.Text = r.Cells[9].Value.ToString();
                txtFax.Text = r.Cells[10].Value.ToString();


            }
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            bool chk;
            if (valid())
            {
                try
                {
                    chk=UpdateCustomer();
                    if (chk==true)
                    {
                        MessageBox.Show("UPDATE Successfull", "Congratulation");
                        loadData();
                        clear();
                    }else
	                {
                        MessageBox.Show("UPDATE Failed");
                        loadData();
                        clear();
	                }
                    
                  
                }
                catch (Exception ex)
                {

                    MessageBox.Show(ex.Message);
                }
            }
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            bool del;
            if (MessageBox.Show("Are you sure to DELETE?", "WARNING", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
            {

                del = deleteCustomer();
                if (del == true)
                {
                    loadData();
                    clear();
                    MessageBox.Show("DELETE successful!");
                }
                else
                {
                    loadData();
                    clear();
                    MessageBox.Show("DELETE Failed!");
                }
            }
        }

        private void btnNew_Click(object sender, EventArgs e)
        {
            clear();
        }

        private void btnReload_Click(object sender, EventArgs e)
        {
            loadData();
        }



        private void btnFilter_Click(object sender, EventArgs e)
        {
            if (radTitle.Checked)
                searchByTitile();
            if (radcountry.Checked)
            {
                searchbyCountry();
            }
        }

        private void radTitle_CheckedChanged(object sender, EventArgs e)
        {
            InitializeAOObject();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_gettitleCus";
            SqlDataReader dr = cmd.ExecuteReader();
            List<String> set = new List<string>();
            while (dr.Read())
            {
                set.Add(dr[0].ToString());
            }
            cbbOption.DataSource = set;
        }

        private void radcountry_CheckedChanged(object sender, EventArgs e)
        {
            InitializeAOObject();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_countryCus";
            SqlDataReader dr = cmd.ExecuteReader();
            List<String> set = new List<string>();
            while (dr.Read())
            {
                set.Add(dr[0].ToString());
            }
            cbbOption.DataSource = set;
        }

        private void frmCustomers_Load(object sender, EventArgs e)
        {
            List<string> cbbfilterdata = new List<string>();
            cbbfilterdata.Add("--Choose search option--");
            cbbfilterdata.Add("ID");
            cbbfilterdata.Add("CONTACT NAME");
            cbbFilter.DataSource = cbbfilterdata;

        }

        private void cbbFilter_SelectedIndexChanged(object sender, EventArgs e)
        {

        }
    }
}
